<?php
define('S_USERID', 'localhost:3306');
define('DB_SERVER', 'localhost:3306');
define('DB_USERNAME', 'easytt');
define('DB_PASSWORD', '6rulleMuck!34');
define('DB_DATABASE', 'EasyTimeTracker');
$db = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
if (mysqli_connect_errno()) {
   printf("Verbindung zur Datenbank fehlgeschlagen: %s\n", mysqli_connect_error());
   exit();
}
//Language Settings
define('LANG', 'en');

//Brute-Force counter measures
//An Attempt is failed login or trying to directly call a php before auth or beyond your level
define('ATTEMPT_LIMIT_PER_INTERVAL', 3);
//Time in Hours that must pass since the last infraction before interaction is allowed again 
define('ATTEMPT_LIMIT_RESET_INTERVAL_HRS', 3);


//Min- LEVEL definitions
define('LEVEL_LOGIN', 1);
define('LEVEL_BILL', 50);

define('LEVEL_CREATE_TASK', 15);
define('LEVEL_RETIRE_TASK', 15);
define('LEVEL_EDIT_TASK', 10);
define('LEVEL_UNRETIRE_TASK', 19);


define('LEVEL_CREATE_PROJECT', 25);
define('LEVEL_RETIRE_PROJECT', 25);
define('LEVEL_EDIT_PROJECT', 20);
define('LEVEL_UNRETIRE_PROJECT', 100);

define('LEVEL_CREATE_CUSTOMER', 35);
define('LEVEL_RETIRE_CUSTOMER', 35);
define('LEVEL_EDIT_CUSTOMER', 30);
//NOTE: Projects of Retired Customers are invisible if userlevel is below LEVEL_UNRETIRE_CUSTOMER regardless of LEVEL_UNRETIRE_PROJECT
//Projects of active Customers are untouched
define('LEVEL_UNRETIRE_CUSTOMER', 100);

define('LEVEL_CREATE_USER', 100);
define('LEVEL_RETIRE_USER', 100);
define('LEVEL_EDIT_USER', 100);



define('MYSQL_DATE_FORMAT', 'Y:m:d H:i:s');

define('DEFAULT_PWMASK', '********');

//Prints the msg from $_SESSION and unsets it after.
function  printMSG()
{
   if (isset($_SESSION['msg'])) {
      if (isset($_SESSION['msgtype'])) {
         echo ("<div class=" . $_SESSION['msgtype'] . ">" . $_SESSION['msg'] . "</div>");
         unset($_SESSION['msgtype']);
      } else
         echo ("<div class=\"msg\" >" . $_SESSION['msg'] . "</div>");
      unset($_SESSION['msg']);
   }
}

//String StartsWith function
function starts_with($str, $start)
{
   if ($str == null)
      return false;
   if ($start == null)
      return false;
   $startlen = strlen($start);
   if ($startlen == 0)
      return false;
   # Test-String shorter that expected start
   if (strlen($str) < strlen($start))
      return false;
   $cut = substr($str, 0, $startlen);
   return strcasecmp($start, $cut) == 0;
}

// Easy DB query function using prepared statements
// can use parameterless statements if $typestring is null or empty
// determines if $statement schould have result on the $sql starting with select 
// Return values: false on error, mysqli_row on select, true otherwise
function get_db_result($db, $sql, $typestr, ...$args)
{
   if (!($stmt = $db->prepare($sql))) {
      printf (RLang("err_db_function_failed"),'$db->prepare', $db->errno, $db->error);
      return false;
   }
   if ($typestr !== null || strlen($typestr) > 0) {
      if (!$stmt->bind_param($typestr, ...$args)) {
         echo ("Parabind failed: (" . $db->errno . ") " . $db->error);
         return false;
      }
   }

   if (!$stmt->execute()) {
      printf (RLang("err_db_function_failed"),'$stmt->execute', $db->errno, $db->error);
      return false;
   }
   $must_have_result = starts_with($sql, "select");
   if ($must_have_result) {
      if (!$result = $stmt->get_result()) {
         printf (RLang("err_db_function_failed"),'$stmt->get_result', $db->errno, $db->error);
         return false;
      }
   }
   $stmt->close();
   if ($must_have_result)
      return $result;
   else
      return true;
}

//Logs an connection
//in $isattempt is true, adds to failedattempts and last_infraction
//rows older that ATTEMPT_LIMIT_RESET_INTERVAL_HRS get overriden
function log_connection($db, $isattempt)
{
   $ip = $_SERVER['REMOTE_ADDR'];
   $sql = "SELECT (TIMESTAMPDIFF(HOUR, last_seen, NOW())) AS age, failedattempts FROM connection_log WHERE ip = ?";
   if (!$result = get_db_result($db, $sql, "s", $ip)) {
      echo ($sql . " ");
      die(mysqli_error($db));
   }
   $rowcnt = mysqli_num_rows($result);
   if ($rowcnt == 0) {
      if ($isattempt)
         $sql = "Insert into connection_log(ip, last_seen, last_infraction, failedattempts) values(?, NOW(), NOW(), 1)";
      else
         $sql = "Insert into connection_log(ip, last_seen) values(?, NOW())";

      if (!$result = get_db_result($db, $sql, "s", $ip)) {
         echo ($sql . " ");
         die(mysqli_error($db));
      }
      if ($isattempt && ATTEMPT_LIMIT_PER_INTERVAL <= 1)
         return false;
   } else {
      $row = mysqli_fetch_row($result);
      //If last seen is too old we assume the IP has been reassigned(or the attacker switched tor nodes)
      //so we reset the attempts to unblock the ip
      if ($row[0] > ATTEMPT_LIMIT_RESET_INTERVAL_HRS)
         if ($isattempt)
            $sql = "Update connection_log set failedattempts = 1, last_seen = NOW(), last_infraction = NOW() where ip = ?";
         else
            $sql = "Update connection_log set failedattempts = 0, last_seen = NOW(), last_infraction = null where ip = ?";
      else //otherwise we update the entry accordingly
         if ($isattempt)
            $sql = "Update connection_log set failedattempts = failedattempts + 1, last_seen = NOW(), last_infraction = NOW() where ip = ?";
         else
            $sql = "Update connection_log set last_seen = NOW() where ip = ?";
      if (!$result = get_db_result($db, $sql, "s", $ip)) {
         echo ($sql . " ");
         die(mysqli_error($db));
      }
      //If the user is above the Limit we block him
      if (ATTEMPT_LIMIT_PER_INTERVAL <= $row[1] + ($isattempt ? 1 : 0)) {
         header("location:ISeeYou.php");
         exit();
         return false;
      }
   }
   return true;
}

//log level violation. happens if the user is trying to load a php directly that is beyond his userlevel
function handle_levelviolation($site, $db, $returnsite)
{
   if (!log_connection($db, true))
      return;
   //since links are level-caped, this is an attempts to go beyond assigned rights. SO LOG IT.
   error_log(sprintf( Rlang("log_level_violation") , $_SESSION['username'], $_SESSION['userid'], $site, $_SERVER['REMOTE_ADDR'], get_safe_servervar("HTTP_X_FORWARDED_FOR")));
   $_SESSION['msgtype'] = "error";
   $_SESSION['msg'] = RLang("err_level_violation");
   header("location:" . $returnsite);
}


//log violation of login. happens when a php site is called directly without calling login.php first.
function handle_loginviolation($site, $db)
{
   if (!log_connection($db, true))
      return;
   error_log( sprintf(RLang("log_not_logged_in") , get_safe_sessionvar('username'), get_safe_sessionvar('userid'),$site, $_SERVER['REMOTE_ADDR'], get_safe_servervar("HTTP_X_FORWARDED_FOR")));
   $_SESSION['msgtype'] = "error";
   $_SESSION['msg'] =  RLang("err_not_logged_in");
   header("Location: login.php");
}

function get_safe_servervar($var)
{
   if (isset($_SERVER[$var]))
      return $_SERVER[$var];
   else
      return "unset(" . $var . ")";
}

function get_safe_sessionvar($var)
{
   if (isset($_SESSION[$var]))
      return $_SESSION[$var];
   else
      return "unset(" . $var . ")";
}

//Validates current userlevel and login-status and logs connections and infractions where applicable
//Sends clients to $retirestate on failure. Except when the attempts-limit is reached.(see log_connection)
function validate($minlevel, $site, $db, $returnsite)
{
   if (!isset($_SESSION['username']) || !isset($_SESSION['userid']) || !isset($_SESSION['userlevel'])) {
      handle_loginviolation($site, $db);
      return false;
   }
   if ($_SESSION['userlevel'] < $minlevel) {
      handle_levelviolation($site, $db, $returnsite);
      return false;
   }
   return log_connection($db, false);
}

// Checks level on create or edit, dependant on whether in id is provied in GET
// On Edit loads entity and gives out error if it does not exist
// returns to maintime.php with error msg on entity not found
//Return value: false on Error, mysqli_row on edit, 0 on create
function prepare_edit($leveledit, $levelcreate, $site, $entity, $table, $db, $returnsite)
{
   $id = -1;
   if (isset($_GET['id']) && $_GET['id'] >= 0)
      $id = $_GET['id'];

   if ($id >= 0) {
      if (!validate($leveledit, $site, $db, $returnsite))
         return false;


      $sql = "SELECT * FROM $table t where t.id = ?";
      if (!$result = get_db_result($db, $sql, "i", $id)) {
         echo ($sql . " ");
         die(mysqli_error($db));
      }
      $rowcnt = mysqli_num_rows($result);
      if ($rowcnt == 0) {
         $_SESSION['msgtype'] = "error";
         $_SESSION['msg'] = sprintf(RLang("err_entity_not_found"), $entity, $id);
         header("Location: " . $returnsite);
         return false;
      }
      $row = mysqli_fetch_array($result, MYSQLI_ASSOC);
      mysqli_free_result($result);
      return $row;
   }

   if (!validate($levelcreate, $site, $db, $returnsite))
      return false;

   return true;
}

// sets the retired date for a given $table on a given $id
// returns to $returnsite with error msg on entity not found
// Return values: false on error(missing id, entity not found), otherwise true
function set_retired($minlevel, $site, $entity, $table, $db, $returnsite, $retirestate = true)
{
   if (!validate($minlevel, $site, $db, $returnsite))
      return false;


   if (isset($_GET['id']) && $_GET['id'] >= 0) {
      $id = $_GET['id'];
      if ($retirestate)
         $sql = "Update $table set retired = NOW() where id = ?";
      else
         $sql = "Update $table set retired = null where id = ?";
      if (!get_db_result($db, $sql, "i", $id)) {
         echo ($sql . " ");
         die(mysqli_error($db));
      }
      $rowcnt = mysqli_affected_rows($db);
      if ($rowcnt == 0) {
         $_SESSION['msgtype'] = "error";
         $_SESSION['msg'] = sprintf(RLang("err_entity_not_found"), $entity, $id);
         header("Location: $returnsite");
         return false;
      }
      return true;
   }
   return false;
}

//Get the difference between 2 datetime objects in hours. False if $stopdatetime is before $startdatetime
//Otherwise Result is round to 3
function get_hour_diff_from_sql_datetime($startdatetime, $stopdatetime)
{
   $start = date_create_from_format("Y-m-d H:i:s", $startdatetime);
   $stop = date_create_from_format("Y-m-d H:i:s", $stopdatetime);
   if ($stop <= $start)
      return false;
   $diff = date_diff($start, $stop);
   $min = (float) $diff->i / 60.0;
   $days = (float) $diff->days * 24.0;
   return round((float) $diff->h + $days + $min, 3);
}

function ELang($key){
   CheckLang();
   if(isset($_SESSION[LANG][$key]))
      echo $_SESSION[LANG][$key];
   else
      echo "Translation Error: $key";
}
function RLang($key){
   CheckLang();
   if(isset($_SESSION[LANG][$key]))
      return $_SESSION[LANG][$key];
   else
      return "Translation Error: $key";
}

function CheckLang() {
   if(!isset($_SESSION[LANG])){
      $langfile = fopen("lang/".LANG.".txt","r");
      $langarr = array();
      while (!feof($langfile) ) {

         $line_of_text = fgets($langfile);
         $parts = explode(':', $line_of_text);
         
         //print $parts[0] . $parts[1]. "<BR>";
         $langarr[$parts[0]] = $parts[1];
      }
        
      fclose($langfile);
      $_SESSION[LANG] = $langarr;
   }
}
